# Name: ExcelCombine.py #
# 
# Author: Zhang Huiyuan #
# Date: 2021.10.21 #
# Version: v0.0.2 #
# 
# -*- coding: UTF-8 -*-

import os
import re
import time
import pandas

def GetAllExcelFilesName(szFileDir):
	if(not szFileDir):
		print('Error: 输入目录为空。\n')
		return []
	else:
		pass

	lszFilesName = os.listdir(szFileDir)

	if(not lszFilesName):
		print('Error: 该目录下没有任何文件。\n')
		return []
	else:
		pass

	lszExcelFilesName = []

	for eachFileName in lszFilesName:
		if(re.search('.xls', eachFileName)):
			lszExcelFilesName.append(szFileDir + '/' + eachFileName)
		else:
			pass

	return lszExcelFilesName

def CombineExcel(lszExcelFilesName, szOutputExcel):
	if(not szOutputExcel):
		print('Error: 输出目录不能为空。\n')
		return 0
	else:
		if(not re.search('.xlsx', szOutputExcel)):
			print("Error: 输出文件名错误，确认扩展名是否为'.xlsx'\n")
			return 0
		else:
			pass

	if(len(lszExcelFilesName) < 2):
		print('Error: 没找到需要合并的Excel文件。\n')
		return 0
	else:
		pass

	print('已找到以下待合并的文件：\n%s\n' %lszExcelFilesName)
	
	try:
		df1stExcelData = pandas.read_excel(lszExcelFilesName[0], sheet_name = 0)
		dfResultDateFrame = df1stExcelData
	except Exception as e:
		print('read_excel failed.%s\n' %e)
		try:
			df1stExcelData = pandas.read_table(lszExcelFilesName[0], encoding = 'gb2312', header = 0)
			dfResultDateFrame = df1stExcelData
		except Exception as e:
			print('read_table failed.%s\n' %e)
			try:
				df1stExcelData = pandas.read_csv(lszExcelFilesName[0])
				dfResultDateFrame = df1stExcelData
			except Exception as e:
				print('read_csv failed.%s\n' %e)
			else:
				pass			
		else:
			pass
	else:
		pass

	for i in range(1, len(lszExcelFilesName)):
		try:
			temp_DataFrame = pandas.read_excel(lszExcelFilesName[i], sheet_name = 0, engine = 'xlrd')
			dfResultDateFrame = dfResultDateFrame.append(temp_DataFrame, ignore_index = True)
		except Exception as e:
			print('read_excel failed.%s\n' %e)
			try:
				temp_DataFrame = pandas.read_table(lszExcelFilesName[i], encoding = 'gb2312', header = 0)
				dfResultDateFrame = dfResultDateFrame.append(temp_DataFrame, ignore_index = True)
			except Exception as e:
				print('read_table failed.%s\n' %e)
				try:
					temp_DataFrame = pandas.read_csv(lszExcelFilesName[i])
					dfResultDateFrame = dfResultDateFrame.append(temp_DataFrame, ignore_index = True)
				except Exception as e:
					print('read_csv failed.%s\n' %e)
				else:
					pass
			else:
				pass
		else:
			pass

	dfResultDateFrame = dfResultDateFrame.drop_duplicates(subset = None, keep = 'first', inplace = False)

	dfResultDateFrame.to_excel(szOutputExcel, index = None)
	time.sleep(0.2)

	print('合并成功，输出文件为：%s\n' %szOutputExcel)
	return 0


if __name__ == '__main__':
	global g_szVersionNum
	g_szVersionNum = 'v0.0.2'

	print('ExcelCombine_%s:\n' %g_szVersionNum)

	# Test
	# szFileDir = r'D:/Desktop/Combine'
	# szOutputExcel = 'D:/Desktop/Combine/Result.xlsx'

	szFileDir = input('请输入待合并文件的文件夹路径：\n')
	szOutputDir = input('请输入输出文件的路径：\n')
	szOutputExcel = szOutputDir + '/' + '合并结果.xlsx'

	lszExcelFilesName = GetAllExcelFilesName(szFileDir)
	CombineExcel(lszExcelFilesName, szOutputExcel)

	szSystemPause = input()